package jdbc.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import jdbc.pojo.Brand;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;

/**
 * 品牌数据增删改查
 */
public class BrandTest {
    /**
     * 查询所有数据
     * 1.SQL: select * from tb_brand
     * 2.参数：不需要
     * 3.结果：List<Brand>
     */
    @Test
    public void testSelectAll() throws Exception {
        // 3.加载配置文件
        Properties prop = new Properties();
        InputStream inputStream = BrandTest.class.getClassLoader().getResourceAsStream("druid.properties");
        prop.load(inputStream);
        inputStream.close();

        // 4.获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        // 5.获取数据库连接Connection
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement("select * from tb_brand");
        ResultSet rs = ps.executeQuery();
        ArrayList<Brand> list = new ArrayList<>();
        while (rs.next()) {
            Brand brand = new Brand(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getInt(6)
            );
            list.add(brand);
        }
        System.out.println(list);
        rs.close();
        ps.close();
        connection.close();
    }

    /**
     * 添加数据
     * 1.SQl: insert into tb_brand (brand_name,company_name,ordered,description,status) values (?,?,?,?,?)
     * 2.参数:需要除id以外的参数
     * 3.结果:boolean
     */
    @Test
    public void testAdd() throws Exception {
        // 接受页面传递过来的参数
        String brandName = "马牛逼";
        String companyName = "马牛逼有限公司";
        int ordered = 8;
        String description = "我叫马牛逼,我敢吃屎,我一天吃三斤屎,越吃越牛逼!";
        int status = 1;

        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\\Project-IDEA\\Study_java\\AdvancedCode\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement("insert into tb_brand (brand_name,company_name,ordered,description,status) " +
                "values (?,?,?,?,?) ");
        ps.setString(1, brandName);
        ps.setString(2, companyName);
        ps.setInt(3, ordered);
        ps.setString(4, description);
        ps.setInt(5, status);

        int i = ps.executeUpdate();
        System.out.println(i > 0);

        ps.close();
        connection.close();
    }

    /**
     * 修改数据(根据id来修改)
     * 1.SQl: update tb_brand set brand_name = ?, company_name = ?, ordered = ?, description = ?, status = ? where id = ?
     * 2.参数:所有参数
     * 3.结果:boolean
     */
    @Test
    public void testUpdate() throws Exception {
        // 接受页面传递过来的参数
        int id = 4;
        String brandName = "马牛逼plus";
        String companyName = "马牛逼plus有限公司";
        int ordered = 5;
        String description = "我叫马牛逼,我敢吃屎,我一天吃三公斤屎,越吃越牛逼!(🚇,老人,📱)";
        int status = 1;

        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\\Project-IDEA\\Study_java\\AdvancedCode\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement("update tb_brand " +
                "set brand_name = ?, company_name = ?, ordered = ?, description = ?, status = ? where id = ?");
        ps.setString(1, brandName);
        ps.setString(2, companyName);
        ps.setInt(3, ordered);
        ps.setString(4, description);
        ps.setInt(5, status);
        ps.setInt(6, id);

        int i = ps.executeUpdate();
        System.out.println(i > 0);

        ps.close();
        connection.close();
    }

    /**
     * 删除数据(根据id来删除)
     * 1.SQl: delete from tb_brand where id = ?
     * 2.参数:id
     * 3.结果:boolean
     */
    @Test
    public void testDeleteById() throws Exception {
        // 接受页面传递过来的参数
        int id = 5;

        Properties prop = new Properties();
        prop.load(new FileInputStream("E:\\Project-IDEA\\Study_java\\AdvancedCode\\src\\druid.properties"));

        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement("delete from tb_brand where id = ?");
        ps.setInt(1, id);

        int i = ps.executeUpdate();
        System.out.println(i > 0);

        ps.close();
        connection.close();
    }
}
